package com.mbc.common.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mbc.common.entities.Currency;
import com.mbc.common.entities.CurrencyRpt;
import com.mbc.common.data.CurrencyDataList;
import com.mbc.common.data.CurrencyReferenceData;

public class CurrencyDAOImpls extends DAO<Currency> implements CurrencyDAO {

	public CurrencyDAOImpls(Connection conn)
	{
		this.conn = conn;
		this.table_name = "Currency";
	}

	@Override
	public void create(Currency entity) throws SQLException {
		query = "insert into " + table_name + " ( CurrencyId, CurrencyCode, Description, Symbol, MyanmarDescription, DecimalPlace) values( ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getCurrencyId());
		pstmt.setString(i++, entity.getCurrencyCode());
		pstmt.setString(i++, entity.getDescription());
		pstmt.setString(i++, entity.getSymbol());
		pstmt.setString(i++, entity.getMyanmarDescription());
		pstmt.setByte(i++, entity.getDecimalPlace());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void update(Currency entity) throws SQLException {
		query = "update " + table_name + " set Description = ?, Symbol = ?, MyanmarDescription = ?, DecimalPlace = ?" +
				" where CurrencyId = " + entity.getCurrencyId();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setString(i++, entity.getDescription());
		pstmt.setString(i++, entity.getSymbol());
		pstmt.setString(i++, entity.getMyanmarDescription());
		pstmt.setByte(i++, entity.getDecimalPlace());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where currencyid = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public Currency find(long key) throws SQLException {
		Currency l_Currency = null;
		query = getSelectQuery() + " where currencyid = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next())
			l_Currency = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_Currency;
	}

	@Override
	public ArrayList<Currency> findAll() throws SQLException {
		ArrayList<Currency> l_CurrencyList = new ArrayList<Currency>();
		query = getSelectQuery();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_CurrencyList.add(readRecord(rs));
		}
		rs.close();
		pstmt.close();
		return l_CurrencyList;
	}

	@Override
	protected Currency readRecord(ResultSet rs) throws SQLException {
		Currency l_entity = new Currency();
		l_entity.setCurrencyId(rs.getLong("CurrencyId"));
		l_entity.setCurrencyCode(rs.getString("CurrencyCode"));
		l_entity.setDescription(rs.getString("Description"));
		l_entity.setSymbol(rs.getString("Symbol"));
		l_entity.setMyanmarDescription(rs.getString("MyanmarDescription"));
		l_entity.setDecimalPlace(rs.getByte("DecimalPlace"));
		return l_entity;
	}

	public CurrencyDataList getList(Currency cri) throws SQLException{
		CurrencyDataList l_DataList = new CurrencyDataList();
		ArrayList<Currency> l_entityList = new ArrayList<Currency>();
		/*String l_cri = " and (CurrencyCode like N'%" + cri.getKeyword() + "%'" + 
		" or Description like N'%" + cri.getKeyword() + "%')";*/

		String l_cri = " and (" + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%')";

		query = "set rowcount " + cri.getSize() +
			" select * from (select row_number() over(order by CurrencyId desc) as [rowno], * from " + table_name +
			" where 1 = 1" + l_cri + ") " + table_name +
			" where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_entityList.add(readRecord(rs));
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		query = "select count(CurrencyId) from " + table_name + " where 1 = 1" + l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()){
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
    }

	@Override
	public ArrayList<CurrencyReferenceData> getCurrencyRefList()
			throws SQLException {
		ArrayList<CurrencyReferenceData> l_list = new ArrayList<CurrencyReferenceData>();
		query = "select currencyid , currencycode, decimalplace from " + table_name;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while(rs.next()){
			CurrencyReferenceData l_refData = new CurrencyReferenceData();
			l_refData.setKey(rs.getLong(1));
			l_refData.setValue(rs.getString(2));
			l_refData.setDecimalPlace(rs.getByte(3));
			l_list.add(l_refData);
		}
		rs.close();
		pstmt.close();
		return l_list;
	}

	@Override
	public ResultSet prepareCurrencyData(String fromCode, String toCode) throws SQLException {
		query = getSelectQuery() + " where currencycode >= '" + fromCode + "' and currencycode <= '" + toCode + "'";
		pstmt = conn.prepareStatement(query);
		return pstmt.executeQuery();
	}
	public ResultSet prepareCurrencyReport(CurrencyRpt info) throws SQLException {
		ResultSet l_rs;
		String l_query = "dbo.sp_cr_CurrencyRpt ?, ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		CurrencyRptParameter(l_pstmt, info);
		l_rs = l_pstmt.executeQuery();
		return l_rs;

	}

	private void CurrencyRptParameter(CallableStatement l_pstmt,
			CurrencyRpt info) throws SQLException {
		int i = 1;

		l_pstmt.setString(i++, info.getFromCurrencyID());
		l_pstmt.setString(i++, info.getToCurrencyID());

	}

	@Override
	public Currency findCurrencyByCode(String code) throws SQLException {
		Currency l_Currency = null;
		query = getSelectQuery() + " where CurrencyCode = N'" + code + "'";
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_Currency = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_Currency;
	}
}
